﻿using System;
using System.Collections.Generic;
using System.Linq;
using HawaiRestaurant.Models.DataContext;
using HawaiRestaurant.Models.Entities;
using HawaiRestaurant.Models.Helper;
using HawaiRestaurant.Models.Query;

namespace HawaiRestaurant.Models.Repositories
{
    public class Repository : IRepository
    {
        private readonly DbDataContext _dataContext = new DbDataContext();

        public IEnumerable<UserEntities> Login(User user)
        {
            string query = String.Format(QueryConstants.LoginQuery, user.UserName, user.Password);
            return _dataContext.ExecuteQuery<UserEntities>(query);
        }

        public IEnumerable<ItemEntities> GetFavorite(int number = 3)
        {
            string query = String.Format(QueryConstants.FavoriteQuery, number);
            return _dataContext.ExecuteQuery<ItemEntities>(query);
        }

        public IEnumerable<ItemEntities> GetNew(int number = 3)
        {
            string query = String.Format(QueryConstants.NewQuery, number);
            return _dataContext.ExecuteQuery<ItemEntities>(query);
        }

        public IEnumerable<FoodEntities> GetFoodDetails(int foodId)
        {
            // Get details
            string query = String.Format(QueryConstants.FoodDetailsQuery, foodId);
            IEnumerable<FoodEntities> result = _dataContext.ExecuteQuery<FoodEntities>(query);

            // Update favorite for item
            string update = String.Format(@"UPDATE Drink
                                            SET Favorite = Favorite + 1
                                            WHERE DrinkId = {0}", foodId);
            _dataContext.ExecuteCommand(update);

            return result;
        }

        public IEnumerable<DrinkEntities> GetDrinkDetails(int drinkId)
        {
            // Get details
            string query = String.Format(QueryConstants.DrinkDetailsQuery, drinkId);

            IEnumerable<DrinkEntities> result = _dataContext.ExecuteQuery<DrinkEntities>(query);

            // Update favorite for item
            string update = String.Format(@"UPDATE Drink
                                            SET Favorite = Favorite + 1
                                            WHERE DrinkId = {0}", drinkId);
            _dataContext.ExecuteCommand(update);

            return result;
        }

        public IEnumerable<ItemTypeEntities> GetCategory()
        {
            return _dataContext.ExecuteQuery<ItemTypeEntities>(QueryConstants.CategoryQuery);
        }

        public string GetOrderPartyConfirm(int contract)
        {
            string template = GetDocumentPath(1); //ORDERPARTYCONFIRM
            string newFile = "E:\\ORDERPARTYCONFIRM" + Guid.NewGuid() + ".docx";
            var entities = new DocumentEntities
            {
                DocumentType = DocumentType.Orderpartyconfirm,
                ListData = new Dictionary<string, string>()
            };

            var orderPaty = GetOrderParty(contract);
            var orderPartyEntities = orderPaty.SingleOrDefault();
            entities.ListData.Add("<Staff_Name>", orderPartyEntities != null ? orderPartyEntities.StaffName : String.Empty);
            entities.ListData.Add("<PhoneNumber>", orderPartyEntities != null ? orderPartyEntities.PhoneNumber : String.Empty);
            entities.ListData.Add("<Cust_Name>", orderPartyEntities != null ? orderPartyEntities.CustName : String.Empty);
            entities.ListData.Add("<OrganizationName>", orderPartyEntities != null ? orderPartyEntities.OrganizationName : String.Empty);
            entities.ListData.Add("<OrganizationAddress>", orderPartyEntities != null ? orderPartyEntities.OrganizationAddress : String.Empty);
            entities.ListData.Add("<Cust_Phone>", orderPartyEntities != null ? orderPartyEntities.CustPhone : String.Empty);
            entities.ListData.Add("<PartyDate>", orderPartyEntities != null ? orderPartyEntities.PartyDate.ToShortDateString() : String.Empty);
            entities.ListData.Add("<PartyTime>", orderPartyEntities != null ? orderPartyEntities.PartyDate.ToShortTimeString() : String.Empty);
            entities.ListData.Add("<TotalDeposit>", orderPartyEntities != null ? orderPartyEntities.TotalDeposit.ToString() : String.Empty);

            for (int i = 1; i <= orderPaty.ToArray().Count(); i++)
            {
                entities.ListData.Add("<Menu" + i + ">", orderPaty.ElementAt(1).FoodName != null ? orderPartyEntities.FoodName : String.Empty);
            }

            new DocumentHelper().GenerateDoc(template, newFile, entities);
            return newFile;
        }

        public IEnumerable<SaleReportEntities> GetSaleReport()
        {
            return _dataContext.ExecuteQuery<SaleReportEntities>(QueryConstants.SaleReportQuery);
        }

        public IEnumerable<SaleReportEntities> GetSaleReport(DateTime fromDate, DateTime toDate)
        {
            var query = String.Format(QueryConstants.SaleReport2Query, fromDate,toDate);
            return _dataContext.ExecuteQuery<SaleReportEntities>(query);
        }

        public IEnumerable<OrderReportEntities> GetOrderReport(DateTime fromDate, DateTime toDate)
        {
            var query = String.Format(QueryConstants.OrderReportQuery, fromDate, toDate);
            return _dataContext.ExecuteQuery<OrderReportEntities>(query);
        }

        public IEnumerable<ReceiptEntities> GetReceipt(int contractId)
        {
            var query = String.Format(QueryConstants.OrderPartyConfirmQuery, contractId);
            return _dataContext.ExecuteQuery<ReceiptEntities>(query);
        }

        public string OrderReport(DateTime fromDate, DateTime toDate)
        {
            string filePath = "C:\\" + Guid.NewGuid() + "_OrderReport.xlsx";
            new ExcelHelper().ExportOrderReport(GetOrderReport(fromDate, toDate), filePath);
            return filePath;
        }

        public string OrderSummaryReport(string[] entities)
        {
            string filePath = "C:\\" + Guid.NewGuid() + "OrderSummaryReport.xls";
            var query = String.Format(QueryConstants.OrderSummaryQuery, entities[0], entities[1], entities[2],
                                      entities[3]);

            var result = _dataContext.ExecuteQuery<OrderSummaryEntities>(query);
            new ExcelHelper().ExportOrderSummaryReport(result,filePath);
            return filePath;
        }

        private string GetDocumentPath(int id)
        {
            var path = _dataContext.LookupItems.SingleOrDefault(a => a.LookupItemTypeId == id);
            return path != null ? path.ToString() : null;
        }

        public IEnumerable<OrderPartyEntities> GetOrderParty(int contractId)
        {
            var query = String.Format(QueryConstants.OrderPartyConfirmQuery, contractId);
            return _dataContext.ExecuteQuery<OrderPartyEntities>(query);
        }

        public int CreateContract(Contract contract)
        {
            try
            {
                _dataContext.Contracts.InsertOnSubmit(contract);
                _dataContext.SubmitChanges();
                return contract.ContractId;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        public bool InsertToContractFood(Contract_Food food)
        {
            try
            {
                _dataContext.Contract_Foods.InsertOnSubmit(food);
                _dataContext.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool InsertToContractDrink(Contract_Drink drink)
        {
            try
            {
                _dataContext.Contract_Drinks.InsertOnSubmit(drink);
                _dataContext.SubmitChanges();
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}